import pandas as pd
import numpy as np
from datetime import datetime


# 工单编号：大数据 - 八维保险数据挖掘 - 08 - 人寿保险风险管理

class InsuranceRiskAnalyzer:
    def __init__(self):
        self.data = self.generate_mock_data()
        self.calculated_metrics = {}

    def generate_mock_data(self):
        """生成模拟保险业务数据（包含风险管理所需字段）"""
        dates = pd.date_range(start='2024-01-01', end='2025-02-28', freq='ME')
        periods = len(dates)

        data = pd.DataFrame({
            # 基础财务数据
            'report_date': dates,
            'owner_equity': np.random.uniform(8000, 30000, periods).round(2),
            'total_assets': np.random.uniform(10000, 50000, periods).round(2),
            'total_liabilities': np.random.uniform(5000, 20000, periods).round(2),

            # 保费相关数据
            'original_premium': np.random.uniform(4500, 13000, periods).round(2),
            'ceded_premium': np.random.uniform(500, 2000, periods).round(2),

            # 准备金相关
            'outstanding_claim_reserve': np.random.uniform(1000, 5000, periods).round(2),
            'health_claim_reserve': np.random.uniform(800, 4000, periods).round(2),
            'deposit_margin': np.random.uniform(200, 1000, periods).round(2),
            'insurance_fund': np.random.uniform(100, 500, periods).round(2),

            # 偿付能力相关
            'admitted_assets': np.random.uniform(12000, 45000, periods).round(2),
            'admitted_liabilities': np.random.uniform(6000, 18000, periods).round(2),
            'minimum_capital': np.random.uniform(3000, 8000, periods).round(2),

            # 资产减值相关
            'bad_debt_reserve': np.random.uniform(100, 800, periods).round(2),
            'impairment_reserve': np.random.uniform(200, 1000, periods).round(2),

            # 其他负债
            'payable_reinsurance': np.random.uniform(300, 1500, periods).round(2),
            'satellite_reserve': np.random.uniform(50, 300, periods).round(2)
        })

        return data.sort_values('report_date')

    def calculate_metrics(self):
        """计算所有风险管理指标"""
        df = self.data.copy()

        # 1. 自留保费占净资产比
        df['net_retained_premium'] = df['original_premium'] - df['ceded_premium']
        df['avg_owner_equity'] = (df['owner_equity'] + df['owner_equity'].shift(1)) / 2
        df['retained_premium_ratio'] = (df['net_retained_premium'] / df['avg_owner_equity']) * 100

        # 2. 自留比率
        df['retention_ratio'] = (df['net_retained_premium'] / df['original_premium']) * 100

        # 3. 未决赔款准备金对净资产比率
        df['outstanding_claim_ratio'] = (df['outstanding_claim_reserve'] / df['owner_equity']) * 100

        # 4. 保险负债占比
        df['insurance_liabilities'] = (df['deposit_margin'] + df['insurance_fund'] +
                                       df['outstanding_claim_reserve'] + df['health_claim_reserve'] +
                                       df['payable_reinsurance'] + df['satellite_reserve'])
        df['total_liabilities_equity'] = df['total_liabilities'] + df['owner_equity']
        df['insurance_liability_ratio'] = (df['insurance_liabilities'] / df['total_liabilities_equity']) * 100

        # 5. 偿付能力充足率
        df['actual_capital'] = df['admitted_assets'] - df['admitted_liabilities']
        df['solvency_ratio'] = (df['actual_capital'] / df['minimum_capital']) * 100

        # 6. 实际资本变化率
        df['actual_capital_growth'] = ((df['actual_capital'] - df['actual_capital'].shift(1)) /
                                       df['actual_capital'].shift(1)) * 100

        # 7. 认可资产负债率
        df['admitted_asset_liability_ratio'] = (df['admitted_liabilities'] / df['admitted_assets']) * 100

        # 8. 保险业务收入规模率
        df['premium_scale_ratio'] = ((df['original_premium'] - df['ceded_premium']) /
                                     df['actual_capital']) * 100

        # 9. 资产减值准备比率
        df['impairment_reserve_ratio'] = ((df['bad_debt_reserve'] + df['impairment_reserve']) /
                                          df['total_assets']) * 100

        self.calculated_metrics = df.dropna().copy()
        return self.calculated_metrics

    def export_to_excel(self, filename="人寿保险风险管理分析.xlsx"):
        """导出结果到Excel文件"""
        if self.calculated_metrics.empty:
            self.calculate_metrics()

        with pd.ExcelWriter(filename, engine='openpyxl') as writer:
            # 主指标表
            metrics_df = self.calculated_metrics[[
                'report_date', 'retained_premium_ratio', 'retention_ratio',
                'outstanding_claim_ratio', 'insurance_liability_ratio',
                'solvency_ratio', 'actual_capital_growth',
                'admitted_asset_liability_ratio', 'premium_scale_ratio',
                'impairment_reserve_ratio'
            ]]
            metrics_df.to_excel(writer, sheet_name='风险管理指标', index=False)

            # 指标解释表
            metrics_explanation = pd.DataFrame({
                '指标名称': [
                    '自留保费占净资产比', '自留比率', '未决赔款准备金对净资产比率',
                    '保险负债占比', '偿付能力充足率', '实际资本变化率',
                    '认可资产负债率', '保险业务收入规模率', '资产减值准备比率'
                ],
                '监管要求': [
                    '≤100%', '无硬性要求', '≤50%',
                    '需匹配负债久期', '>100%', '需保持稳定',
                    '<90%', '无硬性要求', '<5%'
                ],
                '预警阈值': [
                    '>80%时预警', '>90%时关注', '>40%时预警',
                    '>85%时关注', '<120%时预警', '±20%波动预警',
                    '>85%时预警', '>150%时关注', '>3%时关注'
                ]
            })
            metrics_explanation.to_excel(writer, sheet_name='指标解释', index=False)

            # 原始数据表（可选）
            self.data.to_excel(writer, sheet_name='原始数据', index=False)

        print(f"分析结果已导出到: {filename}")
        return filename

    def generate_test_cases(self):
        """生成测试用例（示例）"""
        test_cases = []
        for metric in [
            'retained_premium_ratio', 'solvency_ratio',
            'insurance_liability_ratio'
        ]:
            test_cases.append({
                '测试指标': metric,
                '验证方法': f'检查{metric}是否在合理范围内',
                '通过标准': '无异常值（±3σ原则）'
            })
        return test_cases


# 主执行程序
if __name__ == "__main__":
    print("人寿保险风险管理分析项目")
    print("八维研究院 - 2025年2月")
    print("工单编号：大数据 - 八维保险数据挖掘 - 08 - 人寿保险风险管理\n")

    analyzer = InsuranceRiskAnalyzer()
    print("正在计算风险管理指标...")
    results = analyzer.calculate_metrics()

    print("\n关键指标预览:")
    print(results[[
        'report_date', 'retention_ratio',
        'solvency_ratio', 'insurance_liability_ratio'
    ]].tail())

    output_file = analyzer.export_to_excel()
    print("\n测试用例示例:", analyzer.generate_test_cases())
    print("\n分析完成!")